package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import javax.inject.Named;
import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.springframework.transaction.annotation.Transactional;
import com.dhcc.DRGsHosp.application.HistoryDataTransferApplication;

/**  
* @ClassName: HistoryDataTransferApplicationImpl  
* @Description:近三年外的数据迁移  
* @author Administrator  
* @date 2018年7月5日  
* @author shaozhiyuan
*/
@Named("historyDataTransferApplicationImplqiaoyilong20181026")
@Transactional
public class HistoryDataTransferApplicationImpl implements HistoryDataTransferApplication{
	
	private EntityRepository repository;
	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(EntityRepository.class, "repository");
		}
		return repository;
	}

	/**  
	 * @Description:获取数据库中数据当前最大时间
	 * @author shaozhiyuan 
	 * @date 2018年7月4日  
	 * @version V3.1.0  
	 */  
	@Override
	public String getmaxDateInDRG() {
		StringBuilder sql = new StringBuilder();
		sql.append(" select max(ipinfo.P00290000) from drgs.mr_ipinfo ipinfo \n ");
		List<Object> resultList = getRepository().createSqlQuery(sql.toString()).list();
		if (resultList.size() > 0) {
			return resultList.get(0) != null ? resultList.get(0).toString().substring(0, 10) : "";
		}
		return null;
	}
	/**  
	 * @Description:计算时间段内是否有数据
	 * @author shaozhiyuan 
	 * @date 2018年7月4日  
	 * @version V3.1.0  
	 */  
	@Override
	public int checkMaxDateReduceThreeCount(String mindate) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(1) from drgs.mr_ipinfo ipinfo where ipinfo.P00290000<=to_date('"+mindate+"','yyyy-mm-dd') \n ");
		List<Object> resultList = getRepository().createSqlQuery(sql.toString()).list();
		if (resultList.size() > 0) {
			return resultList.get(0) == null ? 0:Integer.parseInt(resultList.get(0)+"");
		}
		return 0;
	}
	/**  
	 * @Description: 判断是否有近三年前的数据，如果有执行表迁移
	 * @author shaozhiyuan 
	 * @date 2018年7月4日  
	 * @version V3.1.0  
	 */  
	@Override
	public void dataTransfer(String mindate, String table, String type) {
		String sql="";
		
		if("0".equals(type)){
			String originalTable=table.substring(0, table.length()-8);
			String pmainindex="pmainindex";
			if("T_GROUPINGLOG_HISTORY".equals(table)){
				pmainindex="MR_ADM";
			}else if("T_MEDINS_HISTORY".equals(table)){
				pmainindex="PARID";
			}else if("T_OTHER_GROUPINGRESULT_HISTORY".equals(table)){
				pmainindex="MR_ID";
			}else if("T_PATIENTDRG_HISTORY".equals(table)){
				pmainindex="PARID";
			}
			//每次执行操作先将历史表中在现有表里的数据删去
			sql="";
			sql+=" delete from DRGs."+table+" t where t."+pmainindex+" in (  \n";
			sql+=" select distinct a."+pmainindex+" from DRGs."+originalTable+" a left join drgs.mr_ipinfo ip \n";
			sql+=" on a."+pmainindex+"=ip.pmainindex where ip.P00290000<=to_date('"+mindate+"','yyyy-mm-dd')  ) \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			//表插入
			sql="";
			if("MR_APPENDPAGE_HISTORY".equals(table)){
				sql+=" insert into DRGs.MR_APPENDPAGE_HISTORY( \n";   
				sql+=" pmainindex ,p90010000 ,p90010001  ,p90020000  ,p90020001  ,p90031000  ,p90031001  ,p90032000  ,p90041000  ,p90041001   \n";
				sql+=" ,p90042000  ,p90051000  ,p90051001  ,p90052000  ,p90061000  ,p90061001  ,p90062000  ,p90070000  ,p90080000  ,p90080001  \n";
				sql+=" ,p90090000  ,p90090001  ,p90100000  ,p90100001  ,p90110000  ,p90110001  ,p90120000  ,p90120001  ,p90130000  ,p90130001  \n";
				sql+=" ,p90140000  ,p90140001  ,p90150000  ,p90150001  ,p90120201  , p90160000   \n";
				sql+=" ) select \n";
				sql+=" t.pmainindex  ,t.p90010000 ,t.p90010001  ,t.p90020000  ,t.p90020001  ,t.p90031000  ,t.p90031001  ,t.p90032000  ,t.p90041000  ,t.p90041001   \n";
				sql+=" ,t.p90042000  ,t.p90051000  ,t.p90051001  ,t.p90052000  ,t.p90061000  ,t.p90061001  ,t.p90062000  ,t.p90070000  ,t.p90080000  ,t.p90080001  \n";
				sql+=" ,t.p90090000  ,t.p90090001  ,t.p90100000  ,t.p90100001  ,t.p90110000  ,t.p90110001  ,t.p90120000  ,t.p90120001  ,t.p90130000  ,t.p90130001  \n";
				sql+=" ,t.p90140000  ,t.p90140001  ,t.p90150000  ,t.p90150001  ,t.p90120201  ,t.p90160000   \n";
				sql+=" from DRGs.MR_APPENDPAGE t \n";
			}else if("MR_DIAGNOSE_HISTORY".equals(table)){
				sql+=" insert into DRGs.MR_DIAGNOSE_HISTORY( \n";   
				sql+="  pmainindex ,pdtype ,pdindex ,pd0100 ,pd0101 ,pd0200 ,pd0201 ,pd0300 ,pd0301 ,pd0202 ,id  \n";
				sql+=" ) select \n";
				sql+="  t.pmainindex ,t.pdtype ,t.pdindex ,t.pd0100 ,t.pd0101 ,t.pd0200 ,t.pd0201 ,t.pd0300 ,t.pd0301 ,t.pd0202 ,t.id  \n";
				sql+=" from DRGs.MR_DIAGNOSE t \n";
			}else if("MR_EXPENSE_HISTORY".equals(table)){
				sql+=" insert into DRGs.MR_EXPENSE_HISTORY( \n";   
				sql+=" pe0100 ,pe0101 ,pe0200 ,peindex ,petype ,pmainindex ,id  \n";
				sql+=" ) select \n";
				sql+=" t.pe0100 ,t.pe0101 ,t.pe0200 ,t.peindex ,t.petype ,t.pmainindex ,t.id \n";
				sql+=" from DRGs.MR_EXPENSE t \n";
			}else if("MR_EXPENSE_SUM_HISTORY".equals(table)){
				sql+=" insert into DRGs.MR_EXPENSE_SUM_HISTORY( \n";   
				sql+=" pmainindex ,drug_cost ,mater_cost ,drg_med_cost ,drg_nurs_cost ,drg_tech_cost ,drg_drug_cost ,drg_manage_cost \n";
				sql+=" ) select \n";
				sql+=" t.pmainindex ,t.drug_cost  ,t.mater_cost ,t.drg_med_cost ,t.drg_nurs_cost ,t.drg_tech_cost ,t.drg_drug_cost ,t.drg_manage_cost \n";
				sql+=" from DRGs.MR_EXPENSE_SUM t \n";
			}else if("MR_OPERATION_HISTORY".equals(table)){
				sql+=" insert into DRGs.MR_OPERATION_HISTORY( \n";   
				sql+=" pmainindex,potype ,poindex ,po0100 ,po0101 ,po0200,po0300,po0301,po0400 \n";
				sql+=" ,po0401,po0500,po0501,po0600,po0601 ,po0700 ,po0701 ,po0800 ,po0801 ,po0900 ,po0901 ,po1000, \n";
				sql+=" po1001,po1100,po1200,po1201,po1300,po1301,po1400,po1500,po1501,po0201,id \n";
				sql+=" ) select t.pmainindex,t.potype ,t.poindex ,t.po0100 ,t.po0101 ,t.po0200,t.po0300,t.po0301,t.po0400 \n";
				sql+=" ,t.po0401,t.po0500,t.po0501,t.po0600,t.po0601 ,t.po0700 ,t.po0701 ,t.po0800 ,t.po0801 ,t.po0900 ,t.po0901 ,t.po1000 \n";
				sql+=" ,t.po1001,t.po1100,t.po1200,t.po1201,t.po1300,t.po1301,t.po1400,t.po1500,t.po1501,t.po0201,t.id from DRGs.MR_OPERATION t \n";
			}else if("MR_REDISGROUP_HISTORY".equals(table)){
				sql+=" insert into DRGs.MR_REDISGROUP_HISTORY( \n";   
				sql+=" redis_id ,pmainindex ,diasis_code ,drg_code , drg_weight ,ver_code ,is_check ,log ,diasis_order \n";
				sql+=" ) select \n";
				sql+=" t.redis_id ,t.pmainindex ,t.diasis_code ,t.drg_code ,t.drg_weight ,t.ver_code ,t.is_check ,t.log ,t.diasis_order \n";
				sql+=" from DRGs.MR_REDISGROUP t \n";
			}else if("T_GROUPINGLOG_HISTORY".equals(table)){
				sql+=" insert into DRGs.T_GROUPINGLOG_HISTORY( \n";
				sql+=" mr_adm  ,gl_j01  ,gl_j02  ,gl_j03  ,gl_j04  ,gl_j05  ,gl_j06  ,gl_j07  ,gl_j08  ,gl_j08_1 ,gl_j09  ,gl_j09_1 \n";
				sql+=" ,gl_j10_1 ,gl_j10_2 ,gl_j10_3 ,gl_j10_4 ,gl_j10_4_0 ,gl_j10_5 ,gl_j10_6 ,gl_j11  ,gl_oper_code ,gl_oper_name ,gl_j00 \n";
				sql+=" ,ver_code ,gl_j12  ,gl_j13  ,gl_j14  ,gl_j15  ,gl_j16  ,gl_j17  ,gl_j10  ,notin_reason ,ver_region \n";
				sql+=" ) select \n";
				sql+=" t.mr_adm  ,t.gl_j01  ,t.gl_j02  ,t.gl_j03  ,t.gl_j04  ,t.gl_j05  ,t.gl_j06  ,t.gl_j07  ,t.gl_j08 \n";
				sql+=" ,t.gl_j08_1 ,t.gl_j09  ,t.gl_j09_1 ,t.gl_j10_1 ,t.gl_j10_2 ,t.gl_j10_3 ,t.gl_j10_4 ,t.gl_j10_4_0 \n";
				sql+=" ,t.gl_j10_5 ,t.gl_j10_6 ,t.gl_j11  ,t.gl_oper_code ,t.gl_oper_name ,t.gl_j00  ,t.ver_code \n";
				sql+=" ,t.gl_j12  ,t.gl_j13  ,t.gl_j14  ,t.gl_j15  ,t.gl_j16  ,t.gl_j17  ,t.gl_j10 \n";
				sql+=" ,t.notin_reason ,t.ver_region \n";
				sql+=" from DRGs.T_GROUPINGLOG t \n";
			}else if("T_MEDINS_HISTORY".equals(table)){
				sql+=" insert into DRGs.T_MEDINS_HISTORY( \n";
				sql+=" parid ,mr_no ,hi_type ,fund_pay  ,private_pay ,private_pay1 ,private_pay2 ,red_mark \n";
				sql+=" ,out_date ,medical_category ,out_type ,in_date ,refuse_fee ,refuse_reason ,is_abnormal \n";
				sql+=" ) select \n";
				sql+="  t.parid ,t.mr_no ,t.hi_type ,t.fund_pay  ,t.private_pay ,t.private_pay1 ,t.private_pay2 ,t.red_mark \n";
				sql+=" ,t.out_date ,t.medical_category ,t.out_type ,t.in_date ,t.refuse_fee ,t.refuse_reason ,t.is_abnormal \n";
				sql+=" from DRGs.T_MEDINS t \n";
			}else if("T_OTHER_GROUPINGRESULT_HISTORY".equals(table)){
				sql+=" insert into DRGs.T_OTHER_GROUPINGRESULT_HISTORY( \n";
				sql+="  mr_id  ,ver_code ,gr_isicd  ,opers_qy ,opers_adrg ,oper_code ,mdcs_main ,mcc ,diags_cc ,cc  ,adrgs_surg \n";
				sql+=" ,adrgs_pre ,adrgs_opermain ,adrgs_oper ,adrgs_medi ,adrgs_main ,adrgs_diag ,error ,error_log ,group_log \n";
				sql+=" ) select \n";
				sql+=" t.mr_id  ,t.ver_code ,t.gr_isicd  ,t.opers_qy ,t.opers_adrg ,t.oper_code ,t.mdcs_main ,t.mcc ,t.diags_cc ,t.cc  ,t.adrgs_surg \n";
				sql+="  ,t.adrgs_pre ,t.adrgs_opermain ,t.adrgs_oper ,t.adrgs_medi ,t.adrgs_main ,t.adrgs_diag ,t.error ,t.error_log ,t.group_log \n";
				sql+=" from DRGs.T_OTHER_GROUPINGRESULT t \n";
			}else if("T_PATIENTDRG_HISTORY".equals(table)){
				sql+=" insert into DRGs.T_PATIENTDRG_HISTORY( \n";
				sql+=" parid ,pardrg_code ,mdc ,ver_code ,notin_code ,log_info ,notin_reason ,ver_region  \n";
				sql+=" ) select \n";
				sql+=" t.parid ,t.pardrg_code ,t.mdc ,t.ver_code ,t.notin_code ,t.log_info ,t.notin_reason ,t.ver_region \n";
				sql+=" from DRGs.T_PATIENTDRG t \n";
			}
			sql+=" left join drgs.mr_ipinfo ip on t."+pmainindex+"=ip.pmainindex where ip.P00290000<=to_date('"+mindate+"','yyyy-mm-dd')\n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			//原始表删除
			sql="";
			sql+=" delete from DRGs."+originalTable+" t where t."+pmainindex+" in ( \n";
			sql+=" select ip.pmainindex from  drgs.mr_ipinfo ip  where ip.P00290000<=to_date('"+mindate+"','yyyy-mm-dd')  ) \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
		}else{
			sql="";
			sql+=" delete from DRGs.Mr_Baseinfo_History t where t.pmainindex in ( \n";
			sql+=" select distinct a.pmainindex from DRGs.Mr_Baseinfo a left join  drgs.mr_ipinfo ip  \n";
			sql+=" on a.pmainindex=ip.pmainindex where ip.P00290000<=to_date('"+mindate+"','yyyy-mm-dd') ) \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			sql="";
			sql+=" insert into DRGs.Mr_Baseinfo_History( \n";
			sql+=" pmainindex ,p00010000 ,p00010001 ,p00020000 ,p00020001 ,p00030000 ,p00040000 ,p00050000 ,p00060000 ,p00070000 ,p00070001 \n"; 
			sql+=" ,p00080000 ,p00090000 ,p00100000 ,p00110000 ,p00120000 ,p00130000 ,p00130100 ,p00130200 ,p00130300 ,p00140000 ,p00140100 \n"; 
			sql+=" ,p00140200 ,p00150000 ,p00160000 ,p00170000 ,p00180000 ,p00190000 ,p00190100 ,p00190200 ,p00190300 ,p00190400 ,p00190500 \n";
			sql+=" ,p00190600 ,p00200000 ,p00200100 ,p00200200 ,p00200300 ,p00200400 ,p00200500 ,p00210000 ,p00210100 ,p00210200 ,p00220000 \n";
			sql+=" ,p00220100 ,p00220200 ,p00220300 ,p00090001 ,p00090002 ,p00220400 ,case_flag ,push_date \n";
			sql+=" ) select \n";
			sql+=" t.pmainindex ,t.p00010000 ,t.p00010001 ,t.p00020000 ,t.p00020001 ,t.p00030000 ,t.p00040000 ,t.p00050000 ,t.p00060000 ,t.p00070000 ,t.p00070001 \n";
			sql+=" ,t.p00080000 ,t.p00090000 ,t.p00100000 ,t.p00110000 ,t.p00120000 ,t.p00130000 ,t.p00130100 ,t.p00130200 ,t.p00130300 ,t.p00140000 ,t.p00140100 \n";
			sql+=" ,t.p00140200 ,t.p00150000 ,t.p00160000 ,t.p00170000 ,t.p00180000 ,t.p00190000 ,t.p00190100 ,t.p00190200 ,t.p00190300 ,t.p00190400 ,t.p00190500 n";
			sql+=" ,t.p00190600 ,t.p00200000 ,t.p00200100 ,t.p00200200 ,t.p00200300 ,t.p00200400 ,t.p00200500 ,t.p00210000 ,t.p00210100 ,t.p00210200 ,t.p00220000 \n";
			sql+=" ,t.p00220100 ,t.p00220200 ,t.p00220300 ,t.p00090001 ,t.p00090002 ,t.p00220400 ,t.case_flag ,t.push_date \n";
			sql+=" from DRGs.Mr_Baseinfo t \n";
			sql+=" left join drgs.mr_ipinfo ip on t.pmainindex=ip.pmainindex where ip.P00290000<=to_date('"+mindate+"','yyyy-mm-dd') \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			sql="";
			sql+=" delete from DRGs.Mr_Ipinfo_History t where t.pmainindex in ( \n";
			sql+=" select distinct ip.pmainindex from  drgs.mr_ipinfo ip  where ip.P00290000<=to_date('"+mindate+"','yyyy-mm-dd')  ) \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			sql="";
			sql+=" insert into DRGs.Mr_Ipinfo_History (   \n";
			sql+=" pmainindex ,p00230000 ,p00230001 ,p00240000 ,p00240001 ,p00250000 ,p00250100 ,p00260000 ,p00260001 ,p00270000 ,p00270001  \n";
			sql+=" ,p00280000 ,p00280001 ,p00290000 ,p00290100 ,p00300000 ,p00300001 ,p00310000 ,p00310001 ,p00320000 \n";
			sql+=" ,p00330000 ,p00330001 ,p00340000 ,p00350000 ,p00360000 ,p00370000 ,p00380000 ,p00380001 ,p00390000 ,p00400100 ,p00400200  \n";
			sql+=" ,p00400300 ,p00410000 ,p00410001 ,p00420000 ,p00420001 ,p00430000 ,p00430001 ,p00440000 ,p00440001 ,p00450000 ,p00450001 \n";
			sql+=" ,p00460000 ,p00460001 ,p00470000 ,p00470001 ,p00480000 ,p00480001 ,p00490000 ,p00500000 ,p00510000 ,p00520000 \n";
			sql+=" ,p00530000 ,p00540000 ,p00550000 ,p00550101 ,p00550102 ,p00550103 ,p00560000 ,p00570000 ,p00570001 ,p00580000 ,p00580001  \n";
			sql+=" ,p00590000 ,p00590100 ,p00590200 ,p00590300 ,p00590400 ,p00590500 ,p00600100 ,p00600101 ,p00600200 ,p00600201 \n";
			sql+=" ,p00600300 ,p00600301 ,p00600400 ,p00600401 ,p00600500 ,p00600600 ,p00600700 ,p00600800 ,p00600900 ,p00610000 \n";
			sql+=" ,p00610001 ,p00610100 ,p00610200 ,p00610300 ,p00620000 ,p00620001 ,p00620100 ,p00620200 ,p00630000 ,p00630001 \n";
			sql+=" ,p00630100 ,p00640001 ,p00640002 ,p00640003 ,p00650001 ,p00650002 ,p00650003 ,p00700000 ,p00800000 ,p00810000 \n";
			sql+=" ,p00900000 ,p00400101 ,p00400201 ,p00400301 ,p00910000 ,p00920000 ,p00930000 ,p00930001 ,p00930002 ,p00940001  \n";
			sql+=" ,p00940002 ,p00601000 ,p00601001 ,p00950000 ,p00950001 ,p00950002 ,p00960000 ,p00960001 ,p00400401 ,p00900001 \n";
			sql+=" ) select \n";
			sql+=" t.pmainindex ,t.p00230000 ,t.p00230001 ,t.p00240000 ,t.p00240001 ,t.p00250000 ,t.p00250100 ,t.p00260000 ,t.p00260001 ,t.p00270000 ,t.p00270001 \n";
			sql+=" ,t.p00280000 ,t.p00280001 ,t.p00290000 ,t.p00290100 ,t.p00300000 ,t.p00300001 ,t.p00310000 ,t.p00310001 ,t.p00320000 \n";
			sql+=" ,t.p00330000 ,t.p00330001 ,t.p00340000 ,t.p00350000 ,t.p00360000 ,t.p00370000 ,t.p00380000 ,t.p00380001 ,t.p00390000 ,t.p00400100 ,t.p00400200  \n";
			sql+=" ,t.p00400300 ,t.p00410000 ,t.p00410001 ,t.p00420000 ,t.p00420001 ,t.p00430000 ,t.p00430001 ,t.p00440000 ,t.p00440001 ,t.p00450000 ,t.p00450001 \n";
			sql+=" ,t.p00460000 ,t.p00460001 ,t.p00470000 ,t.p00470001 ,t.p00480000 ,t.p00480001 ,t.p00490000 ,t.p00500000 ,t.p00510000 ,t.p00520000 \n";
			sql+=" ,t.p00530000 ,t.p00540000 ,t.p00550000 ,t.p00550101 ,t.p00550102 ,t.p00550103 ,t.p00560000 ,t.p00570000 ,t.p00570001 ,t.p00580000 ,t.p00580001 \n";
			sql+=" ,t.p00590000 ,t.p00590100 ,t.p00590200 ,t.p00590300 ,t.p00590400 ,t.p00590500 ,t.p00600100 ,t.p00600101 ,t.p00600200 ,t.p00600201 \n";
			sql+=" ,t.p00600300 ,t.p00600301 ,t.p00600400 ,t.p00600401 ,t.p00600500 ,t.p00600600 ,t.p00600700 ,t.p00600800 ,t.p00600900 ,t.p00610000 \n";
			sql+=" ,t.p00610001 ,t.p00610100 ,t.p00610200 ,t.p00610300 ,t.p00620000 ,t.p00620001 ,t.p00620100 ,t.p00620200 ,t.p00630000 ,t.p00630001 \n";
			sql+=" ,t.p00630100 ,t.p00640001 ,t.p00640002 ,t.p00640003 ,t.p00650001 ,t.p00650002 ,t.p00650003 ,t.p00700000 ,t.p00800000 ,t.p00810000 \n";
			sql+=" ,t.p00900000 ,t.p00400101 ,t.p00400201 ,t.p00400301 ,t.p00910000 ,t.p00920000 ,t.p00930000 ,t.p00930001 ,t.p00930002 ,t.p00940001 \n";
			sql+=" ,t.p00940002 ,t.p00601000 ,t.p00601001 ,t.p00950000 ,t.p00950001 ,t.p00950002 ,t.p00960000 ,t.p00960001 ,t.p00400401 ,t.p00900001 \n";
			sql+=" from DRGs.Mr_Ipinfo t where t.P00290000<=to_date('"+mindate+"','yyyy-mm-dd') \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			sql="";
			sql+=" delete from DRGs.Mr_Ipinfo  where P00290000<=to_date('"+mindate+"','yyyy-mm-dd') \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
			sql="";
			sql+=" delete from DRGs.Mr_Baseinfo t where t.pmainindex in ( select ip.pmainindex from  DRGs.Mr_Ipinfo_History ip ) \n";
			getRepository().createSqlQuery(sql.toString()).executeUpdate();
		}
	}

}
